
package linuxbox.bbs.bean;

import java.sql.*;
import java.io.*;
import java.util.*;
import java.text.*;

import linuxbox.BoxStatic;
import linuxbox.exception.*;
import linuxbox.util.*;
import linuxbox.bbs.logic.*;
import linuxbox.bean.*;
import linuxbox.bbs.exception.*;

public class BbsMsgBean extends TableBean {
    private long m_lMsgID, m_lTopicID, m_lParentID;
    private int m_iIndLvl;
    private java.sql.Date m_dtSubDate;
    private java.sql.Time m_tmSubTime;
    private String m_sAuthor = "";
    private String m_sSubject = "";
    private String m_sMsgBody = "";
    private int m_iCategory;
    private String m_sCatName = "";
    private String m_sIP = "";
    private String m_sFilePath ="d:\\props\\hello.txt";
    private byte[] m_byBuf = null;
    private boolean m_bFileLoaded = false;
    private boolean m_bBodyLoaded = false;

    public BbsMsgBean(BbsMsgKey pk, DbAdapter db) {
        this.init(pk, db);
    }

    public BbsMsgBean(BbsMsgKey pk) {
        this.init(pk, null);
    }

    private void init(BbsMsgKey pk, DbAdapter db) {
        super.init(db);
        if (pk != null) {
            m_lMsgID = pk.m_lMsgID;
            m_lTopicID = pk.m_lTopicID;
        }
        super.setTableName("bbs_msg");
    }

    protected void load() throws BbsMsgBeanException {
        if (!m_bLoaded) {
            try {
//==need to modify=========================================
                String sql;
                makeConnection();
                sql = "select topic_id, parent_id, indent_level, sub_date, author, subject,"
                    + " category, ip, cat_name from bbs_msg, bbs_cat where bbs_msg.category = bbs_cat.cat_id and msg_id = " + m_lMsgID;
                //System.out.println(sql + "\n");
                m_db.executeQuery(sql);
                if (m_db.next()) {
                    m_lTopicID = m_db.getLong("topic_id");
                    m_lParentID = m_db.getLong("parent_id");
                    m_iIndLvl = m_db.getInt("indent_level");

                    Timestamp tp = m_db.getTimestamp("sub_date");
                    long lt = tp.getTime();
                    m_dtSubDate = new java.sql.Date(lt);
                    m_tmSubTime = new java.sql.Time(lt);

                    m_sAuthor = m_db.getString("author");
                    m_sSubject = m_db.getString("subject");
                    m_iCategory = m_db.getInt("category");
                    m_sIP = m_db.getString("ip");
                    m_sCatName = m_db.getString("cat_name");
                } else {
                    close();
                    throw new BbsMsgBeanException("Load error: MsgID(" + m_lMsgID + ") not found!");
                }
                m_bLoaded = true;
                close();
//==end================================================
            } catch(DbBeanException e1) {
                throw new BbsMsgBeanException("Can't load the record: " + e1.getMessage());
            } catch(TableBeanException e2) {
                throw new BbsMsgBeanException("Can't load the Police record: " + e2.getMessage());
            }
        }
    }

    protected void loadBody() throws BbsMsgBeanException {
        if (!m_bBodyLoaded) {
            try {
                String sql;
                makeConnection();
                sql = "select msg_body from bbs_msg where msg_id = " + m_lMsgID;
                //System.out.println(sql + "\n");
                m_db.executeQuery(sql);
                if (m_db.next()) {
                    m_sMsgBody = m_db.getString("msg_body");
                } else {
                    close();
                    throw new BbsMsgBeanException("LoadBody error: MsgID(" + m_lMsgID + ") not found!");
                }
                m_bBodyLoaded = true;
                close();
            } catch(DbBeanException e1) {
                throw new BbsMsgBeanException("Can't load the record: " + e1.getMessage());
            } catch(TableBeanException e2) {
                throw new BbsMsgBeanException("Can't load the Police record: " + e2.getMessage());
            }
        }
    }

    protected boolean loadFile() throws BbsMsgBeanException {
        if (!m_bFileLoaded) {
            if(!m_bLoaded)
                return false;

            try {
                String file = m_sFilePath + "\\" + m_iCategory + "\\" + m_lMsgID;
                FileInputStream fi = new FileInputStream(file);
                int len = fi.read(m_byBuf);
                if (len == -1)
                    return false;

                m_bFileLoaded = true;
                fi.close();
                return true;
            } catch(IOException e1) {
                throw new BbsMsgBeanException("Can't load the file: " + e1.getMessage());
            }
        }

        return false;
    }

    public void store() throws BbsMsgBeanException {
        try {
//==need to modify=========================================
            String where = " msg_id = " + m_lMsgID;
//==end================================================
            super.updateTable(where);
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't update in store: " + e2.getMessage());
        }
    }

    public void storeFile() throws BbsMsgBeanException {
        try {
            String file = m_sFilePath + "\\" + m_iCategory + "\\" + m_lMsgID;
            FileOutputStream fo = new FileOutputStream(file);
            fo.write(m_byBuf);
            fo.close();
        } catch(IOException e2) {
            throw new BbsMsgBeanException("Can't update file: " + e2.getMessage());
        }
    }

    public void refresh() throws BbsMsgBeanException {
        m_bLoaded = false;
        load();
    }

    protected void create() throws BbsMsgBeanException {
        try {
//==need to modify=========================================
            if (m_lMsgID != 0) {
                String sql = "INSERT INTO bbs_msg(msg_id, topic_id, parent_id, indent_level) VALUES(" + m_lMsgID + ", 0, 0, 0)";
                makeConnection();
                //System.out.println(sql + "\n");
                m_db.executeUpdate(sql);
            } else {
                String sql = "select msg_id, maxtopic_id from bbs_msg order by msg_id desc, topic_id desc";
                makeConnection();
                m_db.executeQuery(sql);

                if (m_db.next()) {
                    if (m_lTopicID == 0) {
                        long msg_id = m_db.getLong(1);
                        long topic_id = m_db.getLong(2);
                        msg_id++;
                        topic_id++;
                        m_lMsgID = msg_id;
                        m_lTopicID = topic_id;
                        sql = "insert into bbs_msg(msg_id, topic_id, parent_id, indent_level) values("
                            + msg_id + ", " + topic_id + ", 0, 0)";
                        m_db.executeUpdate(sql);
                    } else {
                        long msg_id = m_db.getLong(1);
                        msg_id++;
                        m_lMsgID = msg_id;
                        sql = "insert into bbs_msg(msg_id, topic_id, parent_id, indent_level) values("
                            + msg_id + ", " + m_lTopicID + ", 0, 0)";
                        m_db.executeUpdate(sql);
                    }
                }
            }
            close();
//==end================================================
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't create the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't create the Police record: " + e2.getMessage());
        }
    }

    protected BbsMsgKey create(BbsMsg msg) throws BbsMsgBeanException {
        try {
            String sql = "select max(msg_id), max(topic_id) from bbs_msg ";
            makeConnection();
            m_db.executeQuery(sql);

            BbsMsgKey key = null;

            long nextMsgID = 1;
            long nextTopicID = 1;
            if (m_db.next()) {
                nextMsgID = m_db.getLong(1) + 1;
                nextTopicID = m_db.getLong(2) + 1;
            }

            java.sql.Date sub_date = msg.getSubDate();
            java.sql.Time sub_time = msg.getSubTime();
            String author = msg.getAuthor();
            String subject = msg.getSubject();
            String msg_body = msg.getMsgBody();
            int category = msg.getCategory();
            String ip = msg.getIP();
            long parent_id = -1;
            int indent_level = 0;

            SimpleDateFormat df = new SimpleDateFormat(
                BoxStatic.POSTGRES_DATE_FORMAT);
            String dtStr = df.format(sub_date);
            df = new SimpleDateFormat(
                BoxStatic.POSTGRES_TIME_FORMAT);
            String tmStr = df.format(sub_time);
            dtStr = dtStr + " " + tmStr;

            long topic_id = msg.getTopicID();
            if (topic_id == -1) {
                topic_id = nextTopicID;
            } else {
                parent_id = msg.getParentID();
                indent_level = msg.getIndLvl();
            }

            sql = "insert into bbs_msg(msg_id, topic_id, parent_id, indent_level,"
                + " sub_date, author, subject, msg_body, category, ip)"
                + " values(" + nextMsgID + ", " + topic_id + ", " + parent_id + ", " + indent_level
                + ", '" + dtStr + "', '"
                + author + "', '" + subject + "', '" + msg_body + "', " + category
                + ", '" + ip + "')";

            m_db.executeUpdate(sql);
            key = new BbsMsgKey(nextMsgID, topic_id);

            close();

            return key;
        } catch(DbBeanException e1) {
            //e1.printStackTrace();
            throw new BbsMsgBeanException("Can't create the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            //e2.printStackTrace();
            throw new BbsMsgBeanException("Can't create the Police record: " + e2.getMessage());
        }
    }

    protected void remove() throws BbsMsgBeanException {
        try {
//==need to modify=========================================
            String sql = "DELETE from bbs_msg WHERE msg_id = " + m_lMsgID;
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeUpdate(sql);
            close();
//==end================================================
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't delete the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't delete the Police record: " + e2.getMessage());
        }
    }

    protected BbsMsgKey findByKey() throws BbsMsgBeanException {
        BbsMsgKey pk = null;
        try {
//==need to modify=========================================
            String sql = "select msg_id, topic_id from bbs_msg where msg_id = " + m_lMsgID;
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            if (m_db.next()) {
                pk = new BbsMsgKey();
                pk.setMsgID(m_db.getLong(1), m_db.getLong(2));
            }
            close();
//==end================================================
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return pk;
    }

    protected Vector findBySql(String sqlWhere) throws BbsMsgBeanException {
        Vector v = null;
        try {
//==need to modify=========================================
            String sql = "select msg_id, topic_id from bbs_msg" + sqlWhere;
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            BbsMsgKey pk = null;
            v = new Vector();
            while (m_db.next()) {
                pk = new BbsMsgKey();
                pk.setMsgID(m_db.getLong(1), m_db.getLong(2));
                v.addElement(pk);
            }
            close();
//==end================================================
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return v;
    }

    protected ArrayList findNewList(int iCount) throws BbsMsgBeanException {
        ArrayList v = null;
        String sql = "";
        try {
            /*int count = 0;
            long begin = (long)getMaxMsgID(0);
            long end = begin - (long)iCount;
            if (end > 0) {
                while (count < iCount) {
                    sql = "Select count(msg_id) from bbs_msg, bbs_cat " +
                        "where bbs_msg.category = bbs_cat.cat_id(+) and msg_id>" + end +
                        " and msg_id<=" + begin + "order by msg_id desc";
                    //System.out.println(sql + "\n");
                    makeConnection();
                    m_db.executeQuery(sql);

                    if (m_db.next()) {
                        count = m_db.getInt(1);
                        long tmp = end - (iCount - count);
                        if (tmp > 0)
                            end = tmp;
                        else
                            break;
                    }
                }
            } else
                end = 1;

            sql = "Select msg_id, topic_id from bbs_msg, bbs_cat " +
                "where bbs_msg.category=bbs_cat.cat_id(+) and msg_id>=" + end +
                " and msg_id<=" + begin + " order by bbs_msg.msg_id desc";
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            BbsMsgKey pk = null;
            v = new Vector();
            while (m_db.next()) {
                pk = new BbsMsgKey();
                pk.setMsgID(m_db.getLong(1), m_db.getLong(2));
                v.addElement(pk);
            }
            close();*/

            //sql = "Select msg_id, topic_id from bbs_msg, bbs_cat " +
            //  "where bbs_msg.category=*bbs_cat.cat_id order by bbs_msg.msg_id desc";

            sql = "select msg_id, topic_id from bbs_msg, bbs_cat " +
                "where bbs_msg.category=bbs_cat.cat_id order by bbs_msg.msg_id desc";
            BoxStatic.logger.debug(sql);
            makeConnection();
            m_db.executeQuery(sql);
            BbsMsgKey pk = null;
            v = new ArrayList();

            while (iCount > 0 && m_db.next()) {
                pk = new BbsMsgKey();
                pk.setMsgID(m_db.getLong(1), m_db.getLong(2));
                v.add(pk);
                iCount--;
            }
            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return v;
    }

    protected int getMsgCount(int iCatID) throws BbsMsgBeanException {
        int count = 0;
        try {
            String sql = "";
            if (iCatID == 0) {
                //count the total msg number
                sql = "select count(msg_id) from bbs_msg";
            } else {
                //count the msg number of given category
                sql = "select count(msg_id) from bbs_msg where category=" + iCatID;
            }
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            if (m_db.next())
                count = m_db.getInt(1);

            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return count;
    }

    protected long getTopicCount(int iCatID) throws BbsMsgBeanException {
        long count = 0;
        try {
            String sql = "";
            if (iCatID == 0) {
                //count the total msg number
                sql = "select count(distinct topic_id) from bbs_msg";
            } else {
                //count the msg number of given category
                sql = "select count(distinct topic_id) from bbs_msg where category=" + iCatID;
            }
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            if (m_db.next())
                count = m_db.getLong(1);

            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return count;
    }

    protected long getMaxMsgID(int iCatID) throws BbsMsgBeanException {
        long max = 0;
        try {
            String sql = "";
            if (iCatID == 0) {
                //count the max msg id of all msg
                sql = "select msg_id from bbs_msg order by msg_id desc";
            } else {
                //count the max msg id of given category
                sql = "select msg_id from bbs_msg where category="
                    + iCatID + " order by msg_id desc";
            }
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            if (m_db.next())
                max = m_db.getLong(1);

            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return max;
    }

    protected long getMaxTopicID(int iCatID) throws BbsMsgBeanException {
        long max = 0;
        try {
            String sql = "";
            if (iCatID == 0) {
                //count the max topic id of all msg
                sql = "select topic_id from bbs_msg order by topic_id desc";
            } else {
                //count the max topic id of given category
                sql = "select topic_id from bbs_msg where category="
                    + iCatID + " order by topic_id desc";
            }
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            if (m_db.next())
                max = m_db.getLong(1);

            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return max;
    }

    protected Vector getNext20(long begin, int iCatID) throws BbsMsgBeanException {
        //desc get msg, begin > end

        Vector v = null;
        String sql = "";
        long lStep = 20;

        try {
            if (iCatID == 0) {
                //count the max msg id of all msg
                sql = "select msg_id, topic_id from bbs_msg order by topic_id desc, indent_level, msg_id";
            } else {
                //count the max msg id of given category
                sql = "select msg_id, topic_id from bbs_msg where category=" + iCatID +
                " order by topic_id desc, indent_level, msg_id";
            }
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            BbsMsgKey pk = null;
            v = new Vector();

            while (begin > 0 && m_db.next())
                begin--;

            while (lStep > 0 && m_db.next()) {
                pk = new BbsMsgKey();
                pk.setMsgID(m_db.getLong(1), m_db.getLong(2));
                v.addElement(pk);
                lStep--;
            }
            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return v;
    }

    protected long getRequestTopicID(long lNumber, int iCatID) throws BbsMsgBeanException {
        String sql = "";
        long id = 0;

        try {
            if (iCatID == 0) {
                //count the max msg id of all msg
                sql = "select distinct topic_id from bbs_msg order by topic_id desc";
            } else {
                //count the max msg id of given category
                sql = "select distinct topic_id from bbs_msg where category=" + iCatID +
                " order by topic_id desc";
            }
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);

            while (lNumber > 0 && m_db.next())
                lNumber--;

            if (m_db.next())
                id = m_db.getLong(1);

            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return id;
    }

    protected Vector getMsgListByTopic(long lTopicID) throws BbsMsgBeanException {
        Vector v = null;
        try {
            //String sql = "select msg_id, topic_id from bbs_msg where msg_id>" + end +
            //  " and msg_id<=" + begin + " order by msg_id, indent_level asc";
            String sql = "select msg_id, topic_id from bbs_msg where topic_id =" + lTopicID
                + " order by indent_level, msg_id";

            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            BbsMsgKey pk = null;
            v = new Vector();
            while (m_db.next()) {
                pk = new BbsMsgKey();
                pk.setMsgID(m_db.getLong(1), m_db.getLong(2));
                v.addElement(pk);
            }
            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return v;
    }

    protected boolean hasChildMsg(long lTopicID, long lMsgID) throws BbsMsgBeanException {
        boolean result = false;
        try {
            String sql = "select count(msg_id) from bbs_msg where topic_id =" + lTopicID
                + " and parent_id=" + lMsgID;

            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);

            if (m_db.next()) {
                if (m_db.getInt(1) != 0)
                    result = true;
            }
            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return result;
    }

    protected BbsMsgKey getNextTopic(long lTopicID, int iCatID) throws BbsMsgBeanException {
        BbsMsgKey next = null;

        try {
            String sql = "";
            if (iCatID == 0) {
                //count the max msg id of all msg
                sql = "select msg_id, topic_id from bbs_msg where topic_id>"
                    + lTopicID + " order by msg_id asc, topic_id asc";
            } else {
                //count the max msg id of given category
                sql = "select msg_id, topic_id from bbs_msg where topic_id>"
                    + lTopicID + " and category=" + iCatID + " order by msg_id asc, topic_id asc";
            }
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            if (m_db.next()) {
                try {
                    next = new BbsMsgKey();
                    next.m_lMsgID = m_db.getLong("msg_id");
                    next.m_lTopicID = m_db.getLong("topic_id");
                } catch (Exception e) {
                    BoxStatic.logger.error(e.getMessage());
                }
            }

            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return next;
    }

    protected BbsMsgKey getPrevTopic(long lTopicID, int iCatID) throws BbsMsgBeanException {
        BbsMsgKey prev = null;
        try {
            String sql = "";
            if (iCatID == 0) {
                //count the max msg id of all msg
                sql = "Select topic_id from bbs_msg where topic_id<"
                    + lTopicID + " order by topic_id desc";
            } else {
                //count the max msg id of given category
                sql = "Select topic_id from bbs_msg where topic_id<"
                    + lTopicID + " and category=" + iCatID  + " order by topic_id desc";
            }
            //System.out.println(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            if (m_db.next()) {
                try {
                    prev = new BbsMsgKey();
                    prev.m_lTopicID = m_db.getLong(1);

                    sql = "select msg_id from bbs_msg where topic_id="
                    + prev.m_lTopicID + " order by msg_id asc";

                    m_db.executeQuery(sql);
                    if (m_db.next()) {
                        prev.m_lMsgID = m_db.getLong(1);
                    }
                } catch (Exception e) {
                    BoxStatic.logger.error(e.getMessage());
                }
            }

            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
        return prev;
    }


    protected ArrayList queryByType(int iCatID, int iType, String sQuery)
    throws BbsMsgBeanException {
        ArrayList v = null;
        String sql = "";

        try {
            if (iCatID == 0) {
                sql = "select msg_id, topic_id from bbs_msg, bbs_cat where category=cat_id";

                //Description: 1 -> subject; 10 -> content; 100 -> author
                switch (iType) {
                    case 1: {
                        sql = sql + " and (subject like '%" + sQuery + "%')";
                    }
                    break;

                    case 10: {
                        sql = sql + " and (msg_body like '%" + sQuery + "%')";
                    }
                    break;

                    case 11: {
                        sql = sql + " and (subject like '%" + sQuery + "%' or msg_body like '%" + sQuery + "%')";
                    }
                    break;

                    case 100: {
                        sql = sql + " and (author like '%" + sQuery + "%')";
                    }
                    break;

                    case 101: {
                        sql = sql + " and (subject like '%" + sQuery + "%' or author like '%" + sQuery + "%')";
                    }
                    break;

                    case 110: {
                        sql = sql + " and (msg_body like '%" + sQuery + "%' or author like '%" + sQuery + "%')";
                    }
                    break;

                    case 111: {
                        sql = sql + " and (subject like '%" + sQuery + "%' or author like '%" + sQuery +
                            "%' or msg_body like '%" + sQuery + "%')";
                    }
                    break;
                }

                sql = sql + " order by sub_date desc";
                BoxStatic.logger.debug(sql);
                makeConnection();
                m_db.executeQuery(sql);
                BbsMsgKey pk = null;
                v = new ArrayList();
                while (m_db.next()) {
                    pk = new BbsMsgKey();
                    pk.setMsgID(m_db.getLong(1), m_db.getLong(2));
                    v.add(pk);
                }
            } else {
                if (iType == 0) {
                    sql = "select msg_id, topic_id from bbs_msg where category =" + iCatID
                        + " and (subject like '%" + sQuery + "%' or msg_body like '%" + sQuery
                        + "%') order by sub_date desc";

                    BoxStatic.logger.debug(sql);
                    makeConnection();
                    m_db.executeQuery(sql);
                    BbsMsgKey pk = null;
                    v = new ArrayList();
                    while (m_db.next()) {
                        pk = new BbsMsgKey();
                        pk.setMsgID(m_db.getLong(1), m_db.getLong(2));
                        v.add(pk);
                    }
                } else {

                }
            }

            close();
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }

        return v;
    }

    protected long getFirstMsgID(long lTopicID) throws BbsMsgBeanException {
        try {
            long firstMsgID = -1;

            String sql = "select msg_id from bbs_msg where topic_id="
                    + lTopicID + " order by msg_id asc";

            BoxStatic.logger.debug(sql + "\n");
            makeConnection();
            m_db.executeQuery(sql);
            if (m_db.next()) {
                firstMsgID = m_db.getLong(1);
            }

            close();
            return firstMsgID;
        } catch(DbBeanException e1) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e1.getMessage());
        } catch(TableBeanException e2) {
            throw new BbsMsgBeanException("Can't find the Police record: " + e2.getMessage());
        }
    }

    public long getMsgID() {
        return m_lMsgID;
    }

    public long getTopicID() throws BbsMsgBeanException {
        //It does not need to load, because if it = 0,
        //then auto generate one to it;else it already been set.
        load();
        return m_lTopicID;
    }

    public void setTopicID(long str) throws BbsMsgBeanException {
            m_lTopicID = str;
            setLong("topic_id", str);
    }

    public long getParentID() throws BbsMsgBeanException {
        load();
        return m_lParentID;
    }

    public void setParentID(long str) throws BbsMsgBeanException {
            m_lParentID = str;
            setLong("parent_id", str);
    }

    public int getIndLvl() throws BbsMsgBeanException {
        load();
        return m_iIndLvl;
    }

    public void setIndLvl(int str) throws BbsMsgBeanException {
            m_iIndLvl = str;
            setInteger("indent_level", str);
    }

    public java.sql.Date getSubDate() throws BbsMsgBeanException {
        load();
        return m_dtSubDate;
    }

    public java.sql.Time getSubTime() throws BbsMsgBeanException {
        load();
        return m_tmSubTime;
    }

    public void setSubDate(java.sql.Date dt, java.sql.Time tm) throws BbsMsgBeanException {
            m_dtSubDate = dt;
            m_tmSubTime = tm;
            setDate("sub_date", dt, tm);
    }

    public String getAuthor() throws BbsMsgBeanException {
        load();
        return m_sAuthor;
    }

    public void setAuthor(String str) throws BbsMsgBeanException {
            m_sAuthor = str;
            setString("author", str);
    }

    public String getSubject() throws BbsMsgBeanException {
        load();
        return m_sSubject;
    }

    public void setSubject(String str) throws BbsMsgBeanException {
            m_sSubject = str;
            setString("subject", str);
    }

    public String getMsgBody() throws BbsMsgBeanException {
        loadBody();
        return m_sMsgBody;
    }

    public void setMsgBody(String str) throws BbsMsgBeanException {
            m_sMsgBody = str;
            setString("msg_body", str);
    }

    public int getCategory() throws BbsMsgBeanException {
        load();
        return m_iCategory;
    }

    public void setCategory(int str) throws BbsMsgBeanException {
            m_iCategory = str;
            setInteger("category", str);
    }

    public String getCatName() throws BbsMsgBeanException {
        load();
        return m_sCatName;
    }

    public String getIP() throws BbsMsgBeanException {
        load();
        return m_sIP;
    }

    public void setIP(String str) throws BbsMsgBeanException {
            m_sIP = str;
            setString("ip", str);
    }
}
